package inet.db.tennis;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Vector;

import inet.db.pool.*;
import inet.util.Logger;

public class TennisRoundDAO {

	private Logger logger = null;
	private DBPoolX poolX = null;
	public TennisRoundDAO(){
		logger = new Logger(this.getClass().getName());
		try{
			poolX = DBPoolX.getInstance(DBPoolXName.SERVICE_BONGDA);
		}catch(Exception exception){
			logger.log(exception.toString()); 
		}
	}
	
	/**
	 * function get a TennisRound by id
	 * @param id
	 * @return TennisRound
	 */
    public TennisRound getRow(BigDecimal id) {
        Connection conn = null;
        PreparedStatement preStmt = null;
        ResultSet rs = null;
        String strSQL = null;
        TennisRound round = null;
        try {
            conn = poolX.getConnection();
            strSQL =
                //"select ID, PHASE_ID, ROUND_NO, ROUND_CODE, LAST_UPDATED from TENNIS_ROUND where ID = ? ";
	            " select a.ID, a.PHASE_ID, ROUND_NO, ROUND_CODE, a.LAST_UPDATED, b.PHASE_NAME, b.CHAMPION_CODE " +
				" from TENNIS_ROUND a join TENNIS_PHASE b ON a.PHASE_ID = b.ID " +
				" where a.ID = ? ";
            preStmt = conn.prepareStatement(strSQL);
            preStmt.setBigDecimal(1, id);
            rs = preStmt.executeQuery();
            if (rs.next()) {
            	round = new TennisRound();
            	round.setId(rs.getBigDecimal(1));
            	round.setPhaseId(rs.getBigDecimal(2));
            	round.setRoundNo(rs.getInt(3));
            	round.setRoundCode(rs.getString(4));
            	round.setLastUpdated(rs.getTimestamp(5));
            	round.setPhaseName(rs.getString(6));
            	round.setChampionCode(rs.getString(7));
            }
        }
        catch (SQLException e) {
            logger.error("getRow: Error executing SQL " + strSQL + ">>>" + e.toString());
        } finally {
            poolX.releaseConnection(conn, preStmt, rs);
        }
        return round;
    }
    
    /**
     * fucntion get all round
     * @return Vector<TennisRound> 
     * TennisSeason (ID, PHASE_ID, ROUND_NO, ROUND_CODE, LAST_UPDATED)
     */
    public Vector<TennisRound> findAll() {
    	Connection conn = null;
    	PreparedStatement preStmt = null;
    	ResultSet rs = null;
    	String strSQL = null;
    	TennisRound round = null;
    	Vector <TennisRound> vRound = null;
    	
    	try {
    		conn = poolX.getConnection();
    		strSQL =
    			"select ID, PHASE_ID, ROUND_NO, ROUND_CODE, LAST_UPDATED from TENNIS_ROUND ";
    		preStmt = conn.prepareStatement(strSQL);
    		rs = preStmt.executeQuery();
    		vRound = new Vector<TennisRound>();
    		while (rs.next()) {
    			round = new TennisRound();
    			round.setId(rs.getBigDecimal(1));
            	round.setPhaseId(rs.getBigDecimal(2));
            	round.setRoundNo(rs.getInt(3));
            	round.setRoundCode(rs.getString(4));
            	round.setLastUpdated(rs.getTimestamp(5));
    			
            	vRound.addElement(round);
    		}
    	}
    	catch (SQLException e) {
    		logger.error("findAll: Error executing SQL " + strSQL + ">>>" + e.toString());
    	} finally {
    		poolX.releaseConnection(conn, preStmt, rs);
    	}
    	return vRound;
    }
    
    /**
     * function get TennisRound by phase id.
     * @param phaseId
     * @return Vector<TennisRound>
     */
    public Vector<TennisRound> findAll(BigDecimal phaseId) {
    	Connection conn = null;
    	PreparedStatement preStmt = null;
    	ResultSet rs = null;
    	String strSQL = null;
    	TennisRound round = null;
    	Vector <TennisRound> vRound = null;
    	
    	try {
    		conn = poolX.getConnection();
    		strSQL =
    			" select a.ID, a.PHASE_ID, ROUND_NO, ROUND_CODE, a.LAST_UPDATED, b.PHASE_NAME, b.CHAMPION_CODE " +
    			" from TENNIS_ROUND a join TENNIS_PHASE b ON a.PHASE_ID = b.ID " +
    			" where PHASE_ID = ? order by ROUND_NO ";
    		preStmt = conn.prepareStatement(strSQL);
    		preStmt.setBigDecimal(1, phaseId);
    		rs = preStmt.executeQuery();
    		vRound = new Vector<TennisRound>();
    		while (rs.next()) {
    			round = new TennisRound();
    			round.setId(rs.getBigDecimal(1));
    			round.setPhaseId(rs.getBigDecimal(2));
    			round.setRoundNo(rs.getInt(3));
    			round.setRoundCode(rs.getString(4));
    			round.setLastUpdated(rs.getTimestamp(5));
    			round.setPhaseName(rs.getString(6));
    			round.setChampionCode(rs.getString(7));
    			
    			vRound.addElement(round);
    		}
    	}
    	catch (SQLException e) {
    		logger.error("findAll: Error executing SQL " + strSQL + ">>>" + e.toString());
    	} finally {
    		poolX.releaseConnection(conn, preStmt, rs);
    	}
    	return vRound;
    }
    
  
    
    /**
     * function get max round no of phase id
     * @param phaseId
     * @return max round no
     */
    public int getMaxNoOfRound(BigDecimal phaseId) {
    	Connection conn = null;
    	PreparedStatement preStmt = null;
    	ResultSet rs = null;
    	String strSQL = null;
    	int maxRoundNo = 0;
    	
    	try {
    		conn = poolX.getConnection();
    		strSQL =
    			" select max(ROUND_NO) " +
    			" from TENNIS_ROUND " +
    			" where PHASE_ID = ? ";
    		preStmt = conn.prepareStatement(strSQL);
    		preStmt.setBigDecimal(1, phaseId);
    		
    		rs = preStmt.executeQuery();
    		if (rs.next()) {
    			maxRoundNo = rs.getInt(1);
    		}
    	}
    	catch (SQLException e) {
    		logger.error("getMaxNoOfRound: Error executing SQL " + strSQL + ">>>" + e.toString());
    	} finally {
    		poolX.releaseConnection(conn, preStmt, rs);
    	}
    	return maxRoundNo;
    }
	
    /**
     * function insert a TennisRound
     * @param phaseId
     * @param roundNo
     * @param roundCode
     * @return true if insert success else return false.
     */
	public boolean insertRow(BigDecimal phaseId, int roundNo, String roundCode){
		Connection conn = null;
        PreparedStatement preStmt = null;
        boolean result = false;
        String strSQL = null;
        try {
            conn = poolX.getConnection();

        	strSQL =" insert into TENNIS_ROUND( ID, PHASE_ID, ROUND_NO, ROUND_CODE, LAST_UPDATED ) " +
    				" values(TENNIS_ROUND_SEQ.nextval, ?, ?, ?, sysdate) ";
	        preStmt = conn.prepareStatement(strSQL);
	        preStmt.setBigDecimal(1, phaseId);
	        preStmt.setInt(2, roundNo);
	        preStmt.setString(3, roundCode);
	        
	        if (preStmt.executeUpdate()>=1) {
	        	result = true;
	        }
        } catch (Exception ex) {
        	logger.error("insertRow: Error executing SQL " + strSQL + ">>>" + ex.toString());            
        } finally {
        	poolX.releaseConnection(conn, preStmt);
       }
       return result;
	}
	
	/**
	 * function update TennisRound
	 * @param id
	 * @param phaseId
	 * @param roundCode
	 * @return true if update success else return false.
	 */
	public boolean updateRow(BigDecimal id, BigDecimal phaseId, String roundCode){
		Connection conn = null;
        PreparedStatement preStmt = null;
        boolean result = false;
        String strSQL = null;
        try {
        	conn = poolX.getConnection();
        	strSQL =" update TENNIS_ROUND set PHASE_ID = ?, ROUND_CODE = ? " +
        			" where ID = ?";
        	preStmt = conn.prepareStatement(strSQL);
        	preStmt.setBigDecimal(1, phaseId);
        	preStmt.setString(2, roundCode);
        	preStmt.setBigDecimal(3, id);
        	
        	if(preStmt.executeUpdate() > 0){
        		result = true;
        	}
        } catch (Exception ex) {
        	logger.error("updateRow: Error executing SQL " + strSQL + ">>>" + ex.toString());
        } finally {
        	poolX.releaseConnection(conn, preStmt);
       }
        return result;
	}	

	/**
	 * function delete a TennisRound
	 * @param id
	 * @return true if update success else return false.
	 */
	public boolean deleteRow(BigDecimal id){
		Connection conn = null;
		PreparedStatement preStmt = null;
		boolean result = false;
		String strSQL = null;
		try {
			conn = poolX.getConnection();
			strSQL =" delete from TENNIS_ROUND where ID = ? ";
			preStmt = conn.prepareStatement(strSQL);
			preStmt.setBigDecimal(1, id);
			if(preStmt.executeUpdate() > 0){
				result = true;
			}
		} catch (Exception ex) {
			logger.error("deleteRow: Error executing SQL " + strSQL + ">>>" + ex.toString());
		} finally {
			poolX.releaseConnection(conn, preStmt);
		}
		return result;
	}
	
	 
    /**
     * function get TennisRound by phase id.
     * @param phaseId
     * @return Vector<TennisRound>
     * Author: Hoannd
     */
    public Vector<TennisRound> findById(int id) {
    	Connection conn = null;
    	PreparedStatement preStmt = null;
    	ResultSet rs = null;
    	String strSQL = null;
    	TennisRound round = null;
    	Vector <TennisRound> vRound = null;
    	try {
    		conn = poolX.getConnection();
    		strSQL =
    			" select a.ID, a.PHASE_ID, ROUND_NO, ROUND_CODE, a.LAST_UPDATED, b.PHASE_NAME, b.CHAMPION_CODE " +
    			" from TENNIS_ROUND a join TENNIS_PHASE b ON a.PHASE_ID = b.ID " +
    			" where a.ID = ? order by ROUND_NO ";
    		preStmt = conn.prepareStatement(strSQL);
    		preStmt.setInt(1, id);
    		rs = preStmt.executeQuery();
    		vRound = new Vector<TennisRound>();
    		while (rs.next()) {
    			round = new TennisRound();
    			round.setId(rs.getBigDecimal(1));
    			round.setPhaseId(rs.getBigDecimal(2));
    			round.setRoundNo(rs.getInt(3));
    			round.setRoundCode(rs.getString(4));
    			round.setLastUpdated(rs.getTimestamp(5));
    			round.setPhaseName(rs.getString(6));
    			round.setChampionCode(rs.getString(7));
    			
    			vRound.addElement(round);
    		}
    	}
    	catch (SQLException e) {
    		logger.error("findAll: Error executing SQL " + strSQL + ">>>" + e.toString());
    	} finally {
    		poolX.releaseConnection(conn, preStmt, rs);
    	}
    	return vRound;
    }
    /**
     * function get TennisRound by phase id.
     * @param phaseId
     * @return Vector<TennisRound>
     * Author : Hoannd
     */
    public Vector<TennisRound> findTennisRound(String championCode) {
    	Connection conn = null;
    	PreparedStatement preStmt = null;
    	ResultSet rs = null;
    	String strSQL = null;
    	TennisRound round = null;
    	Vector <TennisRound> vRound = null;
    	
    	try {
    		conn = poolX.getConnection();
    		strSQL =
    			" select a.ID, a.PHASE_ID, ROUND_NO, ROUND_CODE, a.LAST_UPDATED, b.PHASE_NAME, b.CHAMPION_CODE " +
    			" from TENNIS_ROUND a join TENNIS_PHASE b ON a.PHASE_ID = b.ID " +
    			" where b.CHAMPION_CODE = ? order by ROUND_NO ";
    		preStmt = conn.prepareStatement(strSQL);
    		preStmt.setString(1, championCode);
    		rs = preStmt.executeQuery();
    		vRound = new Vector<TennisRound>();
    		while (rs.next()) {
    			round = new TennisRound();
    			round.setId(rs.getBigDecimal(1));
    			round.setPhaseId(rs.getBigDecimal(2));
    			round.setRoundNo(rs.getInt(3));
    			round.setRoundCode(rs.getString(4));
    			round.setLastUpdated(rs.getTimestamp(5));
    			round.setPhaseName(rs.getString(6));
    			round.setChampionCode(rs.getString(7));
    			
    			vRound.addElement(round);
    		}
    	}
    	catch (SQLException e) {
    		logger.error("findAll: Error executing SQL " + strSQL + ">>>" + e.toString());
    	} finally {
    		poolX.releaseConnection(conn, preStmt, rs);
    	}
    	return vRound;
    }
    public Vector<TennisRound> findTennisRoundNoAndChampionCode(String championCode,int roundNo) {
    	Connection conn = null;
    	PreparedStatement preStmt = null;
    	ResultSet rs = null;
    	String strSQL = null;
    	TennisRound round = null;
    	Vector <TennisRound> vRound = null;
    	
    	try {
    		conn = poolX.getConnection();
    		strSQL =
    			" select a.ID, a.PHASE_ID, ROUND_NO, ROUND_CODE, a.LAST_UPDATED, b.PHASE_NAME, b.CHAMPION_CODE " +
    			" from TENNIS_ROUND a join TENNIS_PHASE b ON a.PHASE_ID = b.ID " +
    			" where b.CHAMPION_CODE = ?  and ROUND_NO = ?  order by ROUND_NO ";
    		
    		preStmt = conn.prepareStatement(strSQL);
    		preStmt.setString(1, championCode);
    		preStmt.setInt(2, roundNo);
    		rs = preStmt.executeQuery();
    		vRound = new Vector<TennisRound>();
    		while (rs.next()) {
    			round = new TennisRound();
    			round.setId(rs.getBigDecimal(1));
    			round.setPhaseId(rs.getBigDecimal(2));
    			round.setRoundNo(rs.getInt(3));
    			round.setRoundCode(rs.getString(4));
    			round.setLastUpdated(rs.getTimestamp(5));
    			round.setPhaseName(rs.getString(6));
    			round.setChampionCode(rs.getString(7));
    			
    			vRound.addElement(round);
    		}
    	}
    	catch (SQLException e) {
    		logger.error("findAll: Error executing SQL " + strSQL + ">>>" + e.toString());
    	} finally {
    		poolX.releaseConnection(conn, preStmt, rs);
    	}
    	return vRound;
    }
    
    
    /**
     * function get round no of phase id
     * @param phaseId
     * @return max round no
     * Author Hoannd
     */
    public int getRoundNo(int phaseId,String roundCode) {
    	Connection conn = null;
    	PreparedStatement preStmt = null;
    	ResultSet rs = null;
    	String strSQL = null;
    	int roundNo = 0;
    	
    	try {
    		conn = poolX.getConnection();
    		strSQL =
    			" select ROUND_NO,ROUND_CODE " +
    			" from TENNIS_ROUND " +
    			" where PHASE_ID = ? and ROUND_CODE = ? ";
    		preStmt = conn.prepareStatement(strSQL);
    		preStmt.setInt(1, phaseId);
    		preStmt.setString(2, roundCode);
    		
    		rs = preStmt.executeQuery();
    		if (rs.next()) {
    			roundNo = rs.getInt(1);
    		}
    	}
    	catch (SQLException e) {
    		logger.error("getMaxNoOfRound: Error executing SQL " + strSQL + ">>>" + e.toString());
    	} finally {
    		poolX.releaseConnection(conn, preStmt, rs);
    	}
    	return roundNo;
    }
    
    
    /**
     * function get TennisRound by phase id.
     * @param phaseId
     * @return Vector<TennisRound>
     */
    public Vector<TennisRound> findAllRound(BigDecimal phaseId) {
    	Connection conn = null;
    	PreparedStatement preStmt = null;
    	ResultSet rs = null;
    	String strSQL = null;
    	TennisRound round = null;
    	Vector <TennisRound> vRound = null;
    	
    	try {
    		conn = poolX.getConnection();
    		strSQL =
    			" select a.ID, a.PHASE_ID, ROUND_NO, ROUND_CODE, a.LAST_UPDATED, b.PHASE_NAME, b.CHAMPION_CODE " +
    			" from TENNIS_ROUND a join TENNIS_PHASE b ON a.PHASE_ID = b.ID " +
    			" where PHASE_ID = ? order by ROUND_NO ";
    		preStmt = conn.prepareStatement(strSQL);
    		preStmt.setBigDecimal(1, phaseId);
    		rs = preStmt.executeQuery();
    		vRound = new Vector<TennisRound>();
    		while (rs.next()) {
    			round = new TennisRound();
    			round.setId(rs.getBigDecimal(1));
    			round.setPhaseId(rs.getBigDecimal(2));
    			round.setRoundNo(rs.getInt(3));
    			round.setRoundCode(rs.getString(4));
    			round.setLastUpdated(rs.getTimestamp(5));
    			round.setPhaseName(rs.getString(6));
    			round.setChampionCode(rs.getString(7));
    			
    			vRound.addElement(round);
    		}
    	}
    	catch (SQLException e) {
    		logger.error("findAll: Error executing SQL " + strSQL + ">>>" + e.toString());
    	} finally {
    		poolX.releaseConnection(conn, preStmt, rs);
    	}
    	return vRound;
    }
    
    
}
